import sys
import pymysql
from pathlib import Path
from common.log import logger
from common.excel_operate import ExcelOperate
from common.sql_operate import SqlOperate
from config.config import DATA_PATH
from common.common_config import db_name_case, db_ip, db_port, db_username, db_password


class DataTransfer(object):
    def __init__(self, file_path=Path(DATA_PATH) / "test_case.xlsx"):
        self.__file_path = file_path
        self.__excel_instance = ExcelOperate(self.__file_path)

    def get_sheet_names(self):
        self.__sheet_names = self.__excel_instance.sheet_names
        return self.__sheet_names

    def get_data_by_sheet_name(self, sheet_name: str):
        """
        是common.excel_operate.ExcelOperate.read_row_data()方法的变种，其只有读取行数据的功能；
        不仅读取行数据，而且记录每行未载入数据库的数据的行号和flag字段的列号，
        用于后续将这些数据载入数据库后修改flag值，防止下次数据转移时载入重复的数据；
        :param sheet_name: 指定sheet页名称
        :return: 列表类型，返回指定sheet页中未载入数据库的数据，每个数据包括行数据、行号、flag列号
        """
        to_transfer_data = []
        try:
            sheet_names = self.get_sheet_names()
            if sheet_name not in sheet_names:
                logger.error(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] There's no '{sheet_name}' in {self.__file_path}.")
                raise Exception(f"'{sheet_name}' NOT in {self.__file_path} sheet names: {sheet_names}.")
            for each_sheet in sheet_names:
                if each_sheet == sheet_name:
                    result = self.__excel_instance.read_row_data(each_sheet, start_row=2)
                    if result["code"] == "000":
                        data = result["data"]
                        locate_result = self.__excel_instance.locate_cell_position(each_sheet, "flag")
                        if locate_result["code"] == "000":
                            logger.info(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] Succeed in locating 'flag'")
                            flag_column, flag_row = locate_result["data"]
                            number_column = 1
                            for each in data:
                                number_column += 1
                                if not each["flag"] or each["flag"] == 0 or each["flag"] == "":
                                    # logger.info(f"[{self.__class__.__name__}].{sys._getframe().f_code.co_name} \"{each['case_number']}\" hasn't loaded into test database yet")
                                    logger.info(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] hasn't loaded into test database yet")
                                    to_transfer_data.append({"data": each, "data_column": number_column, "flag_row": flag_row})
                                else:
                                    # logger.info(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] \"{each['case_number']}\" has already loaded into test database")
                                    logger.info(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] has already loaded into test database" )
                        else:
                            logger.error(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] Fail to locate 'flag'")
                    else:
                        logger.error(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] Fail to get data from '{self.__file_path}'")
        except Exception as e:
            logger.error(f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] {e}")
        finally:
            return to_transfer_data

    def excel_to_database(self, sheet_name, insert_sqls: list):
        """
        将指定sheet页中的数据载入数据库，并修改flag字段值
        param sheet_name: 指定sheet页名称
        param insert_sqls: 指定载入数据库的sql语句
        return: 无返回值
        """
        for each in insert_sqls:
            insert_sql, column, flag_row = each
            self.__sql_instance = SqlOperate(db_ip, db_name_case, db_username, db_password, db_port)
            sql_result = self.__sql_instance.operate_sql(insert_sql)
            if sql_result["code"] == "000":
                excel_result = self.__excel_instance.write_cell_data(sheet_name, column, flag_row + 1, "1")
                if excel_result["code"] == "000":
                    logger.info(
                        f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] Succeed in setting 'flag' value into '1'")
                else:
                    logger.error(
                        f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] Fail to set 'flag' value into '1'")
            else:
                logger.error(
                    f"[{self.__class__.__name__}.{sys._getframe().f_code.co_name}] \"{test_case['case_number']}\" inserting into database has an error in SQL syntax")


if __name__ == "__main__":
    interface_type = input("""Please specify interface type to load into database (list)\n ["multi_interface", "single_interface", "interface_case", "keyword_mapping"]: """)
    type_to_transfer = eval(interface_type)
    # type_to_transfer = ["multi_interface"]  # ["multi_interface", "single_interface"]

    insert_sql_map = {"single_interface": {"sheet_names": ["login", "realtime_view", "common", "data_analysis", "business_management", "detection_setting", "device_management", "system_management", "logout"],
                                           "insert_sql_template": "insert into single_interface_case(case_number, case_level, case_depth, interface_name, request_method, request_url, request_header, request_params, return_code_to_compare, expect_return_code, json_schema, expect_params, param_to_compare, expect_param_value, json_path) values(\'%s\', %d, %d, \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\');"},
                      "multi_interface": {"sheet_names": ["business_process"],
                                          "insert_sql_template": "insert into multi_interface_case(case_number, interface_name, request_method, request_url, request_header, request_params, json_path, expect_value, pattern, value, pass_value, fixture) values(\'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\');"},
                      "interface_case": {"sheet_names": ["interface_case"],
                                         "insert_sql_template": "insert into interface_case(case_number, case_level, case_depth, case_description, action, param_value, return_code_to_compare, expect_return_code, json_schema, expect_params, param_to_compare, expect_param_value, json_path, key_to_pass, key_to_get, fixture) values(\'%s\', %d, %d, \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\', \'%s\');"},
                      "keyword_mapping": {"sheet_names": ["keyword_mapping"],
                                          "insert_sql_template": "insert into keyword_mapping(keyword, value) values(\'%s\', \'%s\');"}
                      }

    data_transfer_instance = DataTransfer()
    if "single_interface" in type_to_transfer:
        for sheet_name in insert_sql_map.get("single_interface").get("sheet_names"):
            test_case_info = data_transfer_instance.get_data_by_sheet_name(sheet_name=sheet_name)
            insert_sqls = []
            for each in test_case_info:
                test_case = each.get("data")
                column = each.get("data_column")
                flag_row = each.get("flag_row")
                insert_sql = insert_sql_map.get("single_interface").get("insert_sql_template") % (
                                                            test_case["case_number"], int(test_case["case_level"]),
                                                            int(test_case["case_depth"]), test_case["interface_name"],
                                                            test_case["request_method"], test_case["request_url"],
                                                            test_case["request_header"], test_case["request_params"],
                                                            test_case["return_code_to_compare"],
                                                            test_case["expect_return_code"], test_case["json_schema"],
                                                            test_case["expect_params"], test_case["param_to_compare"],
                                                            test_case["expect_param_value"], test_case["json_path"])
                insert_sqls.append((insert_sql, column, flag_row))
            print(insert_sqls)
            data_transfer_instance.excel_to_database(sheet_name, insert_sqls)

    if "multi_interface" in type_to_transfer:
        for sheet_name in insert_sql_map.get("multi_interface").get("sheet_names"):
            print(f"sheet_name: {sheet_name}")
            test_case_info = data_transfer_instance.get_data_by_sheet_name(sheet_name=sheet_name)
            print(f"test_case_info: {test_case_info}")
            insert_sqls = []
            for each in test_case_info:
                test_case = each.get("data")
                column = each.get("data_column")
                flag_row = each.get("flag_row")
                insert_sql = insert_sql_map.get("multi_interface").get("insert_sql_template") % (
                                                    test_case["case_number"], test_case["interface_name"],
                                                    test_case["request_method"], test_case["request_url"],
                                                    test_case["request_header"], test_case["request_params"],
                                                    test_case["json_path"], test_case["expect_value"],
                                                    test_case["pattern"], test_case["value"],
                                                    test_case["pass_value"], test_case["fixture"])
                insert_sqls.append((insert_sql, column, flag_row))
            print(insert_sqls)
            data_transfer_instance.excel_to_database(sheet_name, insert_sqls)

    if "interface_case" in type_to_transfer:
        for sheet_name in insert_sql_map.get("interface_case").get("sheet_names"):
            print(f"sheet_name: {sheet_name}")
            test_case_info = data_transfer_instance.get_data_by_sheet_name(sheet_name=sheet_name)
            print(f"test case info length: {len(test_case_info)}")
            print(f"test_case_info: {test_case_info}")
            insert_sqls = []
            for each in test_case_info:
                test_case = each.get("data")
                column = each.get("data_column")
                flag_row = each.get("flag_row")
                insert_sql = insert_sql_map.get("interface_case").get("insert_sql_template") % (
                                                    test_case["case_number"], int(test_case["case_level"]),
                                                    int(test_case["case_depth"]), test_case["case_description"],
                                                    test_case["action"], test_case["param_value"],
                                                    test_case["return_code_to_compare"],
                                                    test_case["expect_return_code"],
                                                    test_case["json_schema"],
                                                    test_case["expect_params"], test_case["param_to_compare"],
                                                    test_case["expect_param_value"], test_case["json_path"],
                                                    test_case["key_to_pass"], test_case["key_to_get"],
                                                    test_case["fixture"])

                insert_sqls.append((insert_sql, column, flag_row))
            print(insert_sqls)
            data_transfer_instance.excel_to_database(sheet_name, insert_sqls)

    if "keyword_mapping" in type_to_transfer:
        for sheet_name in insert_sql_map.get("keyword_mapping").get("sheet_names"):
            print(f"sheet_name: {sheet_name}")
            test_case_info = data_transfer_instance.get_data_by_sheet_name(sheet_name=sheet_name)
            print(f"test_case_info: {test_case_info}")
            insert_sqls = []
            for each in test_case_info:
                test_case = each.get("data")
                column = each.get("data_column")
                flag_row = each.get("flag_row")
                insert_sql = insert_sql_map.get("keyword_mapping").get("insert_sql_template") % (
                                                    test_case["KEYWORD"], test_case["VALUE"])
                insert_sqls.append((insert_sql, column, flag_row))
            print(insert_sqls)
            data_transfer_instance.excel_to_database(sheet_name, insert_sqls)
